﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;

namespace EMR_System.Data
{
    public class DataAccessSQL
    {
        public DataAccessSQL() { }

        public string GetConnectionString()
        {
            return @"Data Source=Data\EMR System.sdf";
        }

        /// <summary>
        /// this method sets the command type of the sqlCMD. the choices are either
        /// a stored procedure or query
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="cmdType">Query or Stored Procedure</param>
        /// <param name="cmdText">string query or stored procedure name</param>
        public void SetCommandType(SqlCeCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        /// <summary>
        /// This method runs only inserts or update statements, queries that only 
        /// return a true or false declaring sucess or failure.
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery(SqlCeCommand sqlCmd, string ConnectionString)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
            {
                sqlCmd.Connection = conn;
                conn.Open();
                try
                {
                    sqlCmd.ExecuteNonQuery();
                }
                catch
                {
                    //ER.EnterError(this.ToString(), err.ToString() + " QUERY: " + sqlCmd.CommandText);
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }

            return true;
        }

        /// <summary>
        /// This method takes a query and runs it and returns a string
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public string ExecuteReader(SqlCeCommand sqlCmd, string ConnectionString)
        {
            string data = "";

            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));
            
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
            {
                sqlCmd.Connection = conn;
                conn.Open();
                SqlCeDataReader reader;
                reader = sqlCmd.ExecuteReader();
                reader.Read();
                try
                {
                    data = reader[0].ToString();
                    return data;
                }
                catch
                {
                    data = null;
                    return data;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public int ExecuteScalar(SqlCeCommand sqlCmd, string ConnectionString)
        {
            int data = -1;

            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
            {
                sqlCmd.Connection = conn;
                conn.Open();
                try
                {
                    sqlCmd.ExecuteNonQuery();

                    SetCommandType(sqlCmd, CommandType.Text, "SELECT @@IDENTITY");
                    data = Convert.ToInt32(sqlCmd.ExecuteScalar());
                }
                catch
                {
                    //ER.EnterError(this.ToString(), err.ToString() + " QUERY: " + sqlCmd.CommandText);
                    return -1;
                }
                finally
                {
                    conn.Close();
                }
            }

            return data;
        }

        /// <summary>
        /// This method takes a query and runs it and returns a string
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public List<string> ExecuteReaderList(SqlCeCommand sqlCmd, string ConnectionString)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));
            
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
            {
                sqlCmd.Connection = conn;
                conn.Open();
                SqlCeDataReader reader;
                reader = sqlCmd.ExecuteReader();
                List<string> tempList = new List<string>();

                while (reader.Read())
                {
                    tempList.Add(reader[0].ToString());
                }

                conn.Close();
                return tempList;
            }
        }

        public List<string>[] ExecuteReaderListString(SqlCeCommand sqlCmd, string ConnectionString)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));
            
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlCeConnection conn = new SqlCeConnection(ConnectionString))
            {
                sqlCmd.Connection = conn;
                conn.Open();
                SqlCeDataReader reader;
                reader = sqlCmd.ExecuteReader();
                List<List<string>> tempList = new List<List<string>>();

                while (reader.Read())
                {
                    List<string> insideList = new List<string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        insideList.Add(reader[i].ToString());
                    }
                    tempList.Add(insideList);
                }

                conn.Close();
                return tempList.ToArray();
            }
        }

        /// <summary>
        /// This method allows you to add a parameter to any stored procedure you are calling
        /// </summary>
        /// <param name="sqlCmd">the SQLCmd you are using to run the stored procedure through</param>
        /// <param name="paramId">The name of the parameter in the stored procedure</param>
        /// <param name="sqlType">This is the database type of the parameter</param>
        /// <param name="paramSize">The database size of the parameter</param>
        /// <param name="paramDirection">wheather this is input or output from the stored procedure</param>
        /// <param name="paramvalue"> actual value of the parameter</param>
        public void AddParamToSQLCmd(SqlCeCommand sqlCmd, string paramId, SqlDbType sqlType, int paramSize, ParameterDirection paramDirection, object paramvalue)
        {
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            if (paramId == string.Empty)
                throw (new ArgumentOutOfRangeException("paramId"));

            SqlCeParameter newSqlParam = new SqlCeParameter();
            newSqlParam.ParameterName = paramId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = paramDirection;

            if (paramSize > 0)
                newSqlParam.Size = paramSize;

            if (paramvalue != null)
                newSqlParam.Value = paramvalue;

            sqlCmd.Parameters.Add(newSqlParam);
        }
    }
}
